﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using System.Data.Common;
using Aspfm.Main;

namespace Aspfm.Dal
{
    class OracleDB : IDbHelper
    {
        public OracleDB(string conn)
        {
            connstr = conn;
        }
        public override DataSourceType GetDbType()
        {
            return DataSourceType.Oracle;
        }

        internal override IDbConnection GetNewConn()
        {
            return new OracleConnection(connstr);
        }
        internal override IDbDataAdapter GetDataAdapter()
        {
            return new OracleDataAdapter();
        }

        internal override DataSet LaodAllColType(string tableName, string columnName = null)
        {
            tableName = tableName.Trim().ToUpper();
            string strSql = string.Format("select column_name,data_type from user_tab_cols where table_name='{0}'", tableName);
            if (tableName.Contains("."))
            {
                string user = tableName.Split('.')[0].Trim().ToUpper();
                string table = tableName.Split('.')[1].Trim().ToUpper();
                strSql = string.Format("select column_name,data_type from all_tab_columns where owner='" + user
                    + "' AND table_name='{0}'", table);
            }
            if (!string.IsNullOrEmpty(columnName))
            {
                strSql += " and column_name='" + columnName.Trim().ToUpper() + "'";
            }
            return QuerySql(strSql);
        }

        public override bool ExistTableOrView(string tableName)
        {
            if (tableName.Contains("."))
            {
                string userdbo = tableName.Substring(0, tableName.LastIndexOf('.'));
                tableName = tableName.Substring(tableName.LastIndexOf('.')+1).Trim().ToUpper();
                return QuerySqlCount("select object_name from all_objects where object_type in ('TABLE','VIEW') and owner='" + userdbo + "' and object_name='" + tableName + "'")>0;
            }
            else
            {
                return QuerySqlCount("select object_name from user_objects where object_type in ('TABLE','VIEW') AND OBJECT_NAME='" + tableName.Trim().ToUpper() + "'") > 0;
            }
        }

        public override DataSet LaodProcParamsType(string procName)
        {
            string sqlstr = @"SELECT argument_name,data_type,sequence colorder,in_out isoutparam,DATA_LENGTH length
            FROM USER_ARGUMENTS where object_name='" + procName.Trim().ToUpper() + "' order by sequence";
            if (procName.Contains("."))
            {
                string procpkg = procName.Substring(0, procName.LastIndexOf('.'));
                procName = procName.Substring(procName.LastIndexOf('.')+1).Trim().ToUpper();
                sqlstr = @"SELECT argument_name,data_type,sequence colorder,in_out isoutparam,DATA_LENGTH length
                FROM USER_ARGUMENTS where object_name='" + procName.Trim().ToUpper() + "' and PACKAGE_NAME='" + procpkg.Trim().ToUpper() + "' order by sequence";
            }
            return QuerySql(sqlstr);
        }

        internal override IDataParameter ParamInit(string paramName, object value, string colType = "VARCHAR2", ParameterDirection paramDir = ParameterDirection.Input, int length = 200)
        {
            OracleParameter iparam = new OracleParameter();
            if (paramDir != ParameterDirection.Input)
            {
                if (length<=0) length = 4000;
                iparam.Size = length;
            }
            iparam.Direction = paramDir;
            iparam.ParameterName = paramName;
            iparam.Value = value;
            iparam.OracleDbType = GetDataTypeByType(colType);
            if (iparam.Value != null)
            {
                iparam.Value = CheckValueType(colType, value, paramName);
            }
            return iparam;
        }
        OracleDbType GetDataTypeByType(string colType = "VARCHAR2")
        {
            colType = colType.Replace(" ", "").Replace("_", "").Replace("2", "").ToUpper();
            if ("BFile".ToUpper() == colType)
            {
                return OracleDbType.BFile;
            }
            else if ("Blob".ToUpper() == colType)
            {
                return OracleDbType.Blob;
            }
            else if ("Byte".ToUpper() == colType)
            {
                return OracleDbType.Byte;
            }
            else if ("Char".ToUpper() == colType)
            {
                return OracleDbType.Char;
            }
            else if ("Clob".ToUpper() == colType)
            {
                return OracleDbType.Clob;
            }
            else if ("Date".ToUpper() == colType)
            {
                return OracleDbType.Date;
            }
            else if ("Decimal".ToUpper() == colType || "NUMBER" == colType)
            {
                return OracleDbType.Decimal;
            }
            else if ("Double".ToUpper() == colType)
            {
                return OracleDbType.Double;
            }
            else if ("Long".ToUpper() == colType)
            {
                return OracleDbType.Long;
            }
            else if ("LongRaw".ToUpper() == colType)
            {
                return OracleDbType.LongRaw;
            }
            else if ("Int16".ToUpper() == colType)
            {
                return OracleDbType.Int16;
            }
            else if ("Int3".ToUpper() == colType)
            {
                return OracleDbType.Int32;
            }
            else if ("Int64".ToUpper() == colType)
            {
                return OracleDbType.Int64;
            }
            else if ("IntervalDS".ToUpper() == colType)
            {
                return OracleDbType.IntervalDS;
            }
            else if ("IntervalYM".ToUpper() == colType)
            {
                return OracleDbType.IntervalYM;
            }
            else if ("NClob".ToUpper() == colType)
            {
                return OracleDbType.NClob;
            }
            else if ("NChar".ToUpper() == colType)
            {
                return OracleDbType.NChar;
            }
            else if ("NVarchar".ToUpper() == colType)
            {
                return OracleDbType.NVarchar2;
            }
            else if ("Raw".ToUpper() == colType)
            {
                return OracleDbType.Raw;
            }
            else if ("RefCursor".ToUpper() == colType)
            {
                return OracleDbType.RefCursor;
            }
            else if ("Single".ToUpper() == colType)
            {
                return OracleDbType.Single;
            }
            else if ("TimeStamp".ToUpper() == colType)
            {
                return OracleDbType.TimeStamp;
            }
            else if ("TimeStampLTZ".ToUpper() == colType)
            {
                return OracleDbType.TimeStampLTZ;
            }
            else if ("TimeStampTZ".ToUpper() == colType)
            {
                return OracleDbType.TimeStampTZ;
            }
            else if ("VARCHAR" == colType)
            {
                return OracleDbType.Varchar2;
            }
            else if ("XmlType".ToUpper() == colType)
            {
                return OracleDbType.XmlType;
            }
            else if ("BinaryDouble".ToUpper() == colType)
            {
                return OracleDbType.BinaryDouble;
            }
            else if ("BinaryFloat".ToUpper() == colType)
            {
                return OracleDbType.BinaryFloat;
            }
            throw new Exception("not find oracletype: " + colType);
        }
        internal override string GetParamsName(string procName)
        {
            if (procName.StartsWith(":"))
            {
                return procName;
            }
            return ":p_" + procName;
        }

        public override DataSet LaodProcAll()
        {
            string sqlstr = @"select * from (select object_name||'.'||procedure_name pname from SYS.USER_PROCEDURES t where object_type='PACKAGE' and length(procedure_name)>0
union select object_name pname from SYS.USER_PROCEDURES t where object_type='PROCEDURE') order by pname";
            return QuerySql(sqlstr);
        }
    }
//    {
//        string connstr = "";
//        string transactionId = "";
//        public override string TransactionId { get { return transactionId; } }
//        OracleTransaction trans = null;
//        OracleConnection conn = null;

//        public OracleDB(string conn)
//        {
//            connstr = conn;
//        }
//        public override void TransactionBegin()
//        {
//            if (trans == null && conn==null)
//            {
//                conn = GetOracleConnection();
//                conn.Open();
//                trans = conn.BeginTransaction();
//                transactionId = Guid.NewGuid().ToString().ToUpper();
//                Loger.WriteLog(LogType.SQLLog, "开启事务..." + TransactionId);
//            }
//        }
//        public override void TransactionClose(bool isCommit = true)
//        {
//            if (trans != null && trans.Connection!=null)
//            {
//                if (trans.Connection.State == ConnectionState.Open)
//                {
//                    if (isCommit)
//                    {
//                        trans.Commit();
//                        Loger.WriteLog(LogType.SQLLog, "提交事务..." + TransactionId);
//                    }
//                    else
//                    {
//                        trans.Rollback();
//                        Loger.WriteLog(LogType.SQLLog, "回滚事务..." + TransactionId);
//                    }
//                    conn.Close();
//                    conn.Dispose();
//                    trans.Dispose();
//                    trans = null;
//                    conn = null;
//                    transactionId = "";
//                }
//            }
//        }
//        private OracleConnection GetOracleConnection()
//        {
//            if (trans == null)
//            {
//                return new OracleConnection(connstr);
//            }
//            return trans.Connection;
//        }
//        private void ConnectionClose(OracleConnection oracleConnection)
//        {
//            if (oracleConnection != null && trans == null)
//            {
//                oracleConnection.Close();
//                oracleConnection.Dispose();
//            }
//        }
//        private void ConnectionOpen(OracleConnection oracleConnection)
//        {
//            if (oracleConnection != null && oracleConnection.State!= ConnectionState.Open)
//            {
//                oracleConnection.Open();
//            }
//        }

//        public override decimal QuerySqlCount(string sql, List<IDataParameter> dbparameter = null)
//        {
//            DataSet dscount = QuerySql("select count(0) ct from (" + sql + ") t ", dbparameter);
//            return Aspfm.Dal.DbFactory.ConvertDecimal(Aspfm.Dal.DbFactory.GetFirstValue(dscount));
//        }
//        public override DataSet QuerySql(string sql, List<IDataParameter> dbparameter = null)
//        {
//            Exception ex = null;
//            DataSet ds = null;
//            string logMsg = "没有SQL参数";
//            if (!string.IsNullOrEmpty(sql) && sql.Trim().StartsWith("select", StringComparison.CurrentCultureIgnoreCase))
//            {
//                base.StartDate();
//                OracleConnection oracleConnection = this.GetOracleConnection();
//                try
//                {
//                    using (OracleCommand oracleCommand = oracleConnection.CreateCommand())
//                    {
//                        oracleCommand.BindByName = true;
//                        oracleCommand.CommandText = sql;
//                        if (dbparameter != null && dbparameter.Count > 0)
//                        {
//                            logMsg = "  SQL参数：";
//                            for (int i = 0; i < dbparameter.Count; i++)
//                            {
//                                oracleCommand.Parameters.Add((OracleParameter)dbparameter[i]);
//                                logMsg += "[" + dbparameter[i].ParameterName + " ：" + (dbparameter[i].Value == null ? "" : dbparameter[i].Value.ToString()) + " type：" + dbparameter[i].DbType.ToString() + "]";
//                            }
//                        }
//                        using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))
//                        {
//                            da.SelectCommand = oracleCommand;
//                            if (ds == null) ds = new DataSet();
//                            da.Fill(ds);
//                        }
//                    }
//                }
//                catch (Exception _ex)
//                {
//                    ex = _ex;
//                }
//                finally
//                {
//                    ConnectionClose(oracleConnection);
//                }
//                base.StopDate(sql + logMsg, ex);
//            }
//            return ds;
//        }

//        public override bool ExecSql(string sql, List<IDataParameter> dbparameter = null)
//        {
//            bool issuss = false;
//            Exception ex = null;
//            string logMsg = "没有SQL参数";
//            if (!string.IsNullOrEmpty(sql))
//            {
//                base.StartDate();
//                OracleConnection oracleConnection = this.GetOracleConnection();
//                try
//                {
//                    using (OracleCommand oracleCommand = oracleConnection.CreateCommand())
//                    {
//                        oracleCommand.BindByName = true;
//                        oracleCommand.CommandText = sql;
//                        if (dbparameter != null && dbparameter.Count > 0)
//                        {
//                            logMsg = "  SQL参数：";
//                            for (int i = 0; i < dbparameter.Count; i++)
//                            {
//                                oracleCommand.Parameters.Add((OracleParameter)dbparameter[i]);
//                                logMsg += "[" + dbparameter[i].ParameterName + " ：" + (dbparameter[i].Value == null ? "" : dbparameter[i].Value.ToString()) + " type：" + dbparameter[i].DbType.ToString() + "]";
//                            }
//                        }
//                        ConnectionOpen(oracleConnection);
//                        oracleCommand.Transaction = trans;
//                        oracleCommand.ExecuteNonQuery();
//                        issuss = true;
//                    }
//                }
//                catch (Exception _ex)
//                {
//                    TransactionClose(false);
//                    ex = _ex;
//                }
//                finally
//                {
//                    ConnectionClose(oracleConnection);
//                }
//                base.StopDate(sql + logMsg, ex);
//            }
//            return issuss;
//        }

//        public override DataSet ExecProc(string procName, bool IsHadReturn, params object[] dbparameter)
//        {
//            Exception ex = null;
//            DataSet ds = null;
//            //if (!string.IsNullOrEmpty(procName))
//            //{
//            //    string logMsg = "存储过程：" + procName + "  参数：";
//            //    base.StartDate();
//            //    OracleConnection oracleConnection = this.GetOracleConnection();
//            //    try
//            //    {
//            //        using (OracleCommand oracleCommand = oracleConnection.CreateCommand())
//            //        {
//            //            oracleCommand.CommandType = CommandType.StoredProcedure;
//            //            oracleCommand.BindByName = BindByName;
//            //            oracleCommand.CommandText = procName.ToUpper();
//            //            if (dbparameter != null && dbparameter.Count > 0)
//            //            {
//            //                for (int i = 0; i < dbparameter.Count; i++)
//            //                {
//            //                    oracleCommand.Parameters.Add((OracleParameter)dbparameter[i]);
//            //                    logMsg += "[" + dbparameter[i].ParameterName + " ：" + (dbparameter[i].Value == null ? "" : dbparameter[i].Value.ToString()) + " type：" + dbparameter[i].DbType.ToString() + "]";
//            //                }
//            //            }
//            //            if (IsCreateDataSetParams)
//            //            {
//            //                oracleCommand.Parameters.Add(new OracleParameter("CUR_OUT", OracleDbType.RefCursor, ParameterDirection.Output));
//            //            }
//            //            ConnectionOpen(oracleConnection);
//            //            oracleCommand.Transaction = trans;
//            //            using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))
//            //            {
//            //                ds = new DataSet();
//            //                da.Fill(ds);
//            //            }
//            //        }
//            //    }
//            //    catch (Exception _ex)
//            //    {
//            //        TransactionClose(false);
//            //        ex = _ex;
//            //    }
//            //    finally
//            //    {
//            //        ConnectionClose(oracleConnection);
//            //    }
//            //    base.StopDate(logMsg, ex);
//            //}
//            return ds;
//        }
//        public override string QueryPageSql(decimal pageindex, decimal pagesize, string sql, string orderby)
//        {
//            DataSourceList dslist = DbTool.ReadConn();
//            return dslist.GetPageSql(GetDbType(), sql, orderby, pageindex, pagesize);
//        }
//        public override DataSet QueryPage(decimal pageindex, decimal pagesize, string sql, string orderby, List<IDataParameter> dbparameter = null)
//        {
//            return QuerySql(QueryPageSql(pageindex, pagesize, sql, orderby), dbparameter);
////            if (pageindex <= 1) pageindex = 1;
////            decimal endindex = pagesize * pageindex;
////            decimal startindex = pagesize * (pageindex - 1);
////            string sqlpage = @"SELECT * FROM (" + sql + @") 
////WHERE ROWID IN (
////   SELECT rid FROM (
////     SELECT rid, ROWNUM AS rn FROM (
////       SELECT ROWID rid FROM (" + sql + @")
////       " + orderby + @"
////     ) t1 WHERE  ROWNUM<=" + endindex + @"
////   ) t2 WHERE  rn>" + startindex + @"
////) " + orderby;
////            return QuerySql(sqlpage,dbparameter);
//            /*
//             * SELECT * FROM bj_00_00_bjinfo 
//   WHERE ROWID IN (
//      SELECT rid FROM (
//        SELECT rid, ROWNUM AS rn FROM (
//          SELECT ROWID rid FROM bj_00_00_bjinfo
//        --  WHERE bjname LIKE '%%%'
//          ORDER BY bjname
//        ) t1 WHERE  ROWNUM<=20
//      ) t2 WHERE  rn>=0 
//    ) ORDER BY bjname
//             */
//            /*
//             *SELECT * FROM TT LIMIT 21,30 
//             */
//            /*
//             * sql2012以上版本
//             * OFFSET  偏移，跳过
//             * FETCH   取
//             * SELECT ShopName from Shop  ORDER BY ShopName   OFFSET　100000 ROW  FETCH NEXT 50 ROW ONLY
//             */
//        }

//        public override IDataParameter ParamCreate()
//        {
//            return new OracleParameter();
//        }

//        public override bool ExistTableOrView(string tableName)
//        {
//            tableName = tableName.Trim().ToUpper();
//            string strSql = string.Format("select 1 from user_objects where object_type in ('TABLE','VIEW') AND OBJECT_NAME='{0}'", tableName.ToUpper());
//            if (tableName.Contains("."))
//            {
//                string user = tableName.Split('.')[0].Trim().ToUpper();
//                string table = tableName.Split('.')[1].Trim().ToUpper();
//                strSql = string.Format("select 1 from all_objects where object_type in ('TABLE','VIEW') and owner='{0}' and object_name='{1}'", user, table);
//            }
//            System.Data.DataSet dataSet = QuerySql(strSql);
//            if (DbFactory.CheckDSNotNull(dataSet))
//            {
//                return dataSet.Tables[0].Rows.Count == 1;
//            }
//            return false;
//        }

//        public override bool ExistColumn(string tableName, string columnName)
//        {
//            System.Data.DataSet dataSet = QuerySql("select * from " + tableName + " where 1=0");
//            if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables[0].Columns.Contains(columnName.Trim().ToUpper()))
//            {
//                return true;
//            }
//            return false;
//        }

//        public override DataSourceType GetDbType()
//        {
//            return DataSourceType.Oracle;
//        }

//        public override bool SaveData(string tableName, DynObj htValue, string PrimaryKeyCol, bool IsInsertAndUpdate = true)
//        {
//            if (htValue == null || htValue.Values == null || htValue.Values.Count <= 0)
//            {
//                return false;
//            }
//            if (!htValue.Values.ContainsKey(PrimaryKeyCol) || htValue.Values[PrimaryKeyCol] == null || string.IsNullOrEmpty(htValue.Values[PrimaryKeyCol].ToString().Trim()))
//            {
//                throw new Exception("更新：" + tableName + " 主键：" + PrimaryKeyCol + " 为空值，不允许保存。");
//                return false;
//            }
//            string strKey = "";
//            string strValue = "";
//            string strUpdate = "";
//            List<IDataParameter> dbparameter = new List<IDataParameter>();
//            DataSet DataColType = LaodAllColType(tableName);
//            foreach (string key in htValue.Values.Keys)
//            {
//                strUpdate += key + "=:p_" + key + ",";
//                strKey += key + ",";
//                strValue += ":p_" + key + ",";
//                AspfmEnum dbtype = GetDataTypeByColumnName(tableName, key, true, DataColType);
//                dbparameter.Add(base.ParamInit(":p_" + key, htValue.GetPropertyValue(key), dbtype));
//            }
//            strKey = strKey.Trim(',');
//            strValue = strValue.Trim(',');
//            strUpdate = strUpdate.Trim(',');
            
//            string strSql = "";
//            if (!ExistRow(tableName, PrimaryKeyCol, htValue.Values[PrimaryKeyCol]))
//            {
//                if (IsInsertAndUpdate)
//                {
//                    strSql = string.Format("insert into {0} ({1}) values ({2})", tableName, strKey, strValue);
//                }
//                else
//                {
//                    throw new Exception("不存在数据，且没有设置插入。" + "select count(0) from " + tableName + " where " + PrimaryKeyCol + "='" + htValue.Values[PrimaryKeyCol] + "'");
//                }
//            }
//            else
//            {
//                strSql = string.Format("update {0} set {1} where {2}", tableName, strUpdate, PrimaryKeyCol + "=:p_" + PrimaryKeyCol);
//            }
//            return ExecSql(strSql, dbparameter);
//        }
//        public override bool ExistRow(string tableName, string PrimaryKeyCol, object values)
//        {
//            if (values == null)
//            {
//                throw new Exception("表：" + tableName + " 主键：" + PrimaryKeyCol + " 为空值。");
//                return false;
//            }
//            List<IDataParameter> dbparameter = new List<IDataParameter>();
//            AspfmEnum dbtype = GetDataTypeByColumnName(tableName, PrimaryKeyCol,false);
//            dbparameter.Add(base.ParamInit(":p_PrimaryKeyCol", values, dbtype));
//            DataSet dsexe = QuerySql("select count(0) from " + tableName + " where " + PrimaryKeyCol + "=:p_PrimaryKeyCol", dbparameter);
//            if (Aspfm.Dal.DbFactory.GetFirstValue(dsexe) == "0")
//            {
//                return false;
//            }
//            return true;
//        }

//        public override AspfmEnum GetDataTypeByColumnName(string tableName, string columnName, bool isFromDS=true, DataSet DataColType = null)
//        {
//            tableName = tableName.Trim().ToUpper();
//            if (DataColType == null && isFromDS)
//            {
//                DataColType = LaodAllColType(tableName);
//            }
//            else
//            {
//                DataColType = LaodAllColType(tableName, columnName);
//            }
//            if (DbFactory.CheckDSHadRow(DataColType))
//            {
//                DataRow[] drs = DataColType.Tables[0].Select("column_name='" + columnName.Trim().ToUpper() + "'");
//                if (drs != null && drs.Length > 0)
//                {
//                    return base.GetDataTypeByType(drs[0]["data_type"].ToString());
//                }
//            }
//            return AspfmEnum.str;
//        }

//        public override DataSet LaodAllColType(string tableName, string columnName=null)
//        {
//            tableName = tableName.Trim().ToUpper();
//            string strSql = string.Format("select column_name,data_type from user_tab_cols where table_name='{0}'", tableName);
//            if (tableName.Contains("."))
//            {
//                string user = tableName.Split('.')[0].Trim().ToUpper();
//                string table = tableName.Split('.')[1].Trim().ToUpper();
//                strSql = string.Format("select column_name,data_type from all_tab_columns where owner='" + user
//                    + "' AND table_name='{0}'", table);
//            }
//            if (!string.IsNullOrEmpty(columnName))
//            {
//                strSql += " and column_name='" + columnName.Trim().ToUpper() + "'";
//            }
//            return QuerySql(strSql);
//        }
//        public override decimal GetPkValue(string mark1, string mark2 = "pk", string Pkid = null)
//        {
//            List<IDataParameter> dbparameter = new List<IDataParameter>();
//            dbparameter.Add(base.ParamInit(":p_v_mark1", mark1));
//            dbparameter.Add(base.ParamInit(":p_v_mark2", mark1));
//            dbparameter.Add(base.ParamInit(":p_v_CacheVal", Pkid));
//            dbparameter.Add(base.ParamInit(":p_v_newnum", null,AspfmEnum.number,ParameterDirection.Output));
//            //DataSet dsexe = ExecProc("pronewidnum",dbparameter,false,false);
//            throw new Exception("没有测试");
//        }
//    }
}

